﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SisEagle.Common.Code;



namespace SisEagleDO
{

    /// <summary>
    /// Classe DO: Data Objects
    /// Criador: Rafael da Silva França
    /// Criada em 08/09/2012
    /// Contato: rafael.sfranca@gmail.com
    /// </summary>
    public class SisEagleDO
    {
        // Atributos
        private SqlConnection oConnection = null;
        private readonly string connectionString = null;
        private SqlDataAdapter oDataAdapter = null;
        private SqlCommand oCommand = null;
        private DataSet oDataSet = null;
        private string strServidor = null;
        public int regAffect = 0;

        //Construtor
        public SisEagleDO()
        {

            connectionString = @"Data Source=.;AttachDbFilename=D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SisEagle.mdf;Initial Catalog=SisEagle;Integrated Security=True";
            //connectionString = @"Data Source=IMAGE070512\DEVSRV;Initial Catalog=novo;Integrated Security=True";
            try
            {
                oConnection = new SqlConnection(connectionString);
                oConnection.Open();
            }
            catch (Exception ex)
            {

                throw new Exception("Nao foi possivel conecetar com o banco de dados", ex);
            }


        }
        //Métodos
        /// <summary>
        /// Executa comandos sql e retorna o número de linhas afetadas.
        /// </summary>
        /// <param name="sSQL">Comando sql</param>
        /// <returns>int regAffect</returns>
        /// <returns>int regAffect</returns>
        public int ExecutaQuery(string sSQL)
        {
            using (SqlTransaction transacao = oConnection.BeginTransaction())
            {
                try
                {
                    oCommand = new SqlCommand(sSQL, oConnection, transacao);


                    regAffect = oCommand.ExecuteNonQuery();

                    if (regAffect == 0)
                    {
                        transacao.Rollback();
                        throw new Exception("ocorreu um erro, entre em contato com o administrador do sistema.");

                    }
                    else
                    {
                        transacao.Commit();
                        return regAffect;

                    }
                }
                catch (Exception err)
                {
                    throw err;
                }

                finally
                {
                    oConnection.Dispose();
                    oCommand.Dispose();
                }


            }

        }

        /// <summary>
        /// Retorna um data set apartir de um comando sql
        /// </summary>
        /// <param name="command">Comando sql</param>
        /// <param name="table">Nome da tabela</param>
        /// <returns>DataSet oDataSet</returns>
        public DataSet GetDataSet(string command, string table)
        {

            try
            {
                //oConnection.Open();
                oCommand = new SqlCommand(command, oConnection);
                oDataAdapter = new SqlDataAdapter(oCommand);
                oDataSet = new DataSet();
                oDataAdapter.Fill(oDataSet, table);
                return oDataSet;
            }
            catch (SqlException err)
            {
                throw err;
            }
            finally
            {
                oConnection.Dispose();
                oCommand.Dispose();
                oDataAdapter.Dispose();
            }
        }

        /// <summary>
        /// Executa select no _banco
        /// </summary>
        /// <param name="command"></param>
        /// <returns>DataReader oCommand.ExecuteReader()</returns>
        public SqlDataReader QueryConsulta(string command)
        {
            try
            {
                oCommand = new SqlCommand(command, oConnection);
                return oCommand.ExecuteReader();
            }
            catch (Exception err)
            {
                throw err;
            }

        }

        public void CloseConn()
        {
            oConnection.Dispose();
        }
    }
}
